package com.trainingcloud.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Vector;

import com.trainingcloud.entity.Role;
import com.trainingcloud.entity.User;
import com.trainingcloud.exception.DAOException;

public class UserDAO extends BaseDAO
{
	private void fillUser(ResultSet rs, User user) throws SQLException, DAOException
	{
		user.setId(rs.getInt("id"));
		user.setLoginName(rs.getString("login_name"));
		user.setLoginPassword(rs.getString("login_password"));
		user.setName(rs.getString("name"));
		user.setEmail(rs.getString("email"));
		RoleDAO roleDAO = new RoleDAO();
		user.setRole(roleDAO.getRoleById(rs.getInt("role_id")));
		user.setActive(rs.getBoolean("active"));
		user.setUpdateTime(new Date(rs.getTimestamp("update_time").getTime()));
		user.setCreateTime(new Date(rs.getTimestamp("create_time").getTime()));
		if(rs.getTimestamp("latest_login_time")!=null)
		{
			user.setLatestLoginTime(new Date(rs.getTimestamp("latest_login_time").getTime()));
		}
	}
	
	public User getUserById(int id) throws DAOException
	{
		User user = null;
		
		Connection connection = getConnection();
		try 
		{
			PreparedStatement query = connection.prepareStatement("SELECT * FROM t_user WHERE id=?");
			query.setInt(1, id);
			ResultSet rs= query.executeQuery();
			if(rs.next())
			{
				user = new User();
				fillUser(rs, user);
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
		
		return user;
	}
	
	public User getUserByLoginName(String loginName) throws DAOException
	{
		User user = null;
		
		Connection connection = getConnection();
		try 
		{
			PreparedStatement query = connection.prepareStatement("SELECT * FROM t_user WHERE login_name=?");
			query.setString(1, loginName);
			ResultSet rs= query.executeQuery();
			if(rs.next())
			{
				user = new User();
				fillUser(rs, user);
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
		
		return user;
	}
	
	public Vector<User> getAllUsers() throws DAOException
	{
		Vector<User> users = new Vector<User>();
		
		Connection connection = getConnection();
		try 
		{
			PreparedStatement query = connection.prepareStatement("SELECT * FROM t_user ORDER BY id");
			ResultSet rs= query.executeQuery();
			while(rs.next())
			{
				User user = new User();
				fillUser(rs, user);
				users.add(user);
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
		
		return users;
	}
	
	public void insertUser(User user) throws DAOException
	{
		Connection connection = getConnection();
		try 
		{
			PreparedStatement query = connection.prepareStatement("INSERT INTO t_user (login_name, login_password, name, email, role_id, active, update_time, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
			query.setString(1, user.getLoginName());
			query.setString(2, user.getLoginPassword());
			query.setString(3, user.getName());
			query.setString(4, user.getEmail());
			query.setInt(5, user.getRole().getId());
			query.setBoolean(6, user.isActive());
			query.setTimestamp(7, new java.sql.Timestamp(user.getUpdateTime().getTime()));
			query.setTimestamp(8, new java.sql.Timestamp(user.getCreateTime().getTime()));
			if(!query.execute() && query.getUpdateCount()==1)
			{
				query = connection.prepareStatement("select @@IDENTITY");
				ResultSet rs = query.executeQuery();
				if(rs.next())
				{
					user.setId(rs.getInt(1));
				}
				else
				{
					throw new DAOException("Get last insert user id failed.");
				}
			}
			else
			{
				throw new DAOException("Insert into t_user failed.");
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
	}
	
	public void updateUser(User user) throws DAOException
	{
		Connection connection = getConnection();
		try
		{
			PreparedStatement query = connection.prepareStatement("UPDATE t_user SET login_name=?, login_password=?, name=?, email=?, role_id=?, active=?, update_time=?, latest_login_time=? WHERE id=?");
			query.setString(1, user.getLoginName());
			query.setString(2, user.getLoginPassword());
			query.setString(3, user.getName());
			query.setString(4, user.getEmail());
			query.setInt(5, user.getRole().getId());
			query.setBoolean(6, user.isActive());
			query.setTimestamp(7, new java.sql.Timestamp(user.getUpdateTime().getTime()));
			if(user.getLatestLoginTime()!=null)
				query.setTimestamp(8, new java.sql.Timestamp(user.getLatestLoginTime().getTime()));
			else
				query.setTimestamp(8, null);
			query.setInt(9, user.getId());
			
			if(!query.execute() && query.getUpdateCount()==1)
			{
				// Success 
			}
			else
			{
				throw new DAOException("Update t_user failed.");
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
	}
	
	public void deleteUser(int userId) throws DAOException
	{
		Connection connection = getConnection();
		try
		{
			PreparedStatement query = connection.prepareStatement("DELETE FROM t_user WHERE id=?");
			query.setInt(1, userId);
			if(!query.execute() && query.getUpdateCount()==1)
			{
				// Success
			}
			else
			{
				throw new DAOException("Delete from t_user failed.");
			}
		}
		catch(SQLException e)
		{
			throw new DAOException(e);
		}
		finally
		{
			try
			{
				connection.close();
			}
			catch (SQLException e) 
			{
				throw new DAOException(e);
			}
		}
	}
}
